using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Specialized;
using System.Collections;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
namespace MES
{
    public partial class RandomInformaion : Form
    {
        private static RandomInformaion instance = null;
        private System.Data.DataSet ds = null;       
        private RandomInformaion()
        {
            InitializeComponent();
        }
        public static RandomInformaion createInstance()
        {
            if (instance == null)
            {
                instance = new RandomInformaion();
            }
            return instance;
        }

        private void RandomInformaion_Load(object sender, EventArgs e)
        {          
            this.dateTimePicker1.MinDate = DateTime.Today.AddDays(-14);
            this.dateTimePicker1.MaxDate = DateTime.Today;
            this.dateTimePicker1.Format = DateTimePickerFormat.Custom;
            this.dateTimePicker1.CustomFormat = "yyyy/MM/dd";
            try
            {
                this.cbLines.SelectedIndex = 0;
                this.cbParts.SelectedIndex = 0;
            }
            catch (Exception)
            {
                return;
            }
        }       

     
        private void btnGo_Click(object sender, EventArgs e)
        {
            System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            ConnectionStringsSection section = config.ConnectionStrings;
            dataGridView1.DataSource = "";
            using (SqlConnection conn = new SqlConnection(section.ConnectionStrings["MES.Properties.Settings.CMS"].ToString()))
            {
                btnGo.Enabled = false;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "CMS.M2.Get_MES_Information";               
                cmd.Parameters.Add("@MFG_LINE", SqlDbType.Char, 4).Value = cbLines.SelectedItem.ToString().Trim();
                cmd.Parameters.Add("@FG_PART", SqlDbType.Char, 32).Value = cbParts.SelectedItem.ToString().Trim();
                cmd.Parameters.Add("@MFG_DATE", SqlDbType.Char, 16).Value = this.dateTimePicker1.Text.Trim();
                SqlParameter RES = cmd.Parameters.Add("@RESULT", SqlDbType.Char, 32) ;
                RES.Direction = ParameterDirection.Output;
              
                try
                {
                    conn.Open();
                    System.Data.DataTable dt = new System.Data.DataTable();
                    //PCB_SN CHAR(8),TEST_STAN Char(4),TEST_ITEM char(4),TEST_DESC char(16),TEST_RES char(16)
                    //declare @res table(k02 varchar(56),mfg_date varchar(16),pcb_no varchar(16),sfc_no varchar(16),cust_sno varchar(16),cal1 varchar(16),cal2 varchar(16),cal3 varchar(16),bmu1 varchar(16),bmu2 varchar(16),bmu3 varchar(16),bmu4 varchar(16),bmu5 varchar(16),bmu6 varchar(16),bmu7 varchar(16),bmu8 varchar(16),cpp1 varchar(16),cpp2 varchar(16),cpp3 varchar(16),cpp4 varchar(16),ftt1 varchar(16),ftt2 varchar(16),ftt3 varchar(16),total tinyint default((0)))

                    dt.Columns.Add("MFG_DATE", typeof(System.String));
                    dt.Columns.Add("PCB_NO", typeof(System.String));
                    dt.Columns.Add("CUST_SNO", typeof(System.String));
                    dt.Columns.Add("Current Tolerance Check", typeof(System.String));
                    dt.Columns.Add("Voltage Tolerance Check",  typeof(System.String));
                    dt.Columns.Add("Temperature tolerance check", typeof(System.String));
                    dt.Columns.Add("Shutdown Power consumption", typeof(System.String));
                    dt.Columns.Add("Normal Power Consumption", typeof(System.String));
                    dt.Columns.Add("2nd High", typeof(System.String));
                    dt.Columns.Add("2nd Release", typeof(System.String));
                    dt.Columns.Add("OVP", typeof(System.String));
                    dt.Columns.Add("OVP Release", typeof(System.String));
                    dt.Columns.Add("UVP", typeof(System.String));
                    dt.Columns.Add("UVP Release", typeof(System.String));
                    dt.Columns.Add("Fast Charge", typeof(System.String));
                    dt.Columns.Add("Fast Discharge", typeof(System.String));
                    dt.Columns.Add("Overcharge Current Protection", typeof(System.String));
                    dt.Columns.Add("Overdischarge Current", typeof(System.String));
                    dt.Columns.Add("Fast Charge Test", typeof(System.String));
                    dt.Columns.Add("Fast Discharge Test", typeof(System.String));
                    dt.Columns.Add("IR", typeof(System.String));
                    dt.Columns[2].Unique = true;

                    SqlDataReader dr = cmd.ExecuteReader();
                    //dt.Load(dr);
                    //dr.Close();
                    while (dr.Read())
                    {
                        DataRow datarow =  dt.NewRow();
                        datarow[0] = dr[0];
                        datarow[1] = dr[1];
                        datarow[2] = dr[2];
                        datarow[3] = dr[3];
                        datarow[4] = dr[4];
                        datarow[5] = dr[5];
                        datarow[6] = dr[6];
                        datarow[7] = dr[7];
                        datarow[8] = dr[8];
                        datarow[9] = dr[9];
                        datarow[10] = dr[10];
                        datarow[11] = dr[11];
                        datarow[12] = dr[12];
                        datarow[13] = dr[13];
                        datarow[14] = dr[14];
                        datarow[15] = dr[15];
                        datarow[16] = dr[16];
                        datarow[17] = dr[17];
                        datarow[18] = dr[18];
                        datarow[19] = dr[19];
                        datarow[20] = dr[20];                       
                        dt.Rows.Add(datarow);
                    }
                    dr.Close();
                    ds.Tables.Add(dt);

                    //dataGridView1.DataSource = dt;
                    //btnGo.Enabled = true;
                }
                catch (Exception exe)
                {
                    MessageBox.Show(exe.Message, "Database or Connection error!", MessageBoxButtons.OK);
                    btnGo.Enabled = true;
                    conn.Close();
                }
              
            }        
            
        }
        protected override void  OnClosed(EventArgs e)
        {
             base.OnClosed(e);
             this.MdiParent.Visible = true;
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.DefaultExt = "xls";
            dlg.Filter = "EXCEL File(*.XLS)|*.xls";
            dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
            dlg.ShowDialog();
            string filename = dlg.FileName;
            if (filename.Trim() == "")
            {
                MessageBox.Show("File Name is null or empty!", "Save file error!", MessageBoxButtons.OK);
                return;
            }
            if (dataGridView1.RowCount <= 0)
            {
                MessageBox.Show("There is no data in the grid.", "Save file error!", MessageBoxButtons.OK);
                return;
            }
            if (dataGridView1.ColumnCount <= 0)
            {
                MessageBox.Show("There is no data in the grid.", "Save file error!", MessageBoxButtons.OK);
                return;
            }
            //if file exists,overwrite.
            FileInfo file = new FileInfo(filename);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "Delete failed! ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            } 

            int rowIndex = 0;
            int colIndex = 0;
            int rowCount = dataGridView1.RowCount;
            int colCount = dataGridView1.ColumnCount;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xlbook;
            Microsoft.Office.Interop.Excel.Worksheet xlsheet;
            try
            {
                xlbook = xlApp.Workbooks.Add(true);
                xlsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlbook.ActiveSheet;
                object[,] data = new object[rowCount + 1, colCount];
                //System.Data.DataTable dt = (System.Data.DataTable)dataGridView1.DataSource;
                //get the title
                foreach (DataGridViewColumn col in dataGridView1.Columns)
                {
                    data[rowIndex, colIndex++] = col.HeaderText;
                }
                colIndex = 0;        
                for (rowIndex = 1; rowIndex < rowCount; rowIndex++)
                {         
                    for (colIndex = 0; colIndex < colCount; colIndex++)
                    {       
                        
                        data[rowIndex, colIndex] = dataGridView1[colIndex,rowIndex - 1 ].Value;
                    }
                }
                Range range = xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[rowCount, colCount]);
                range.Value2 = data;
                xlsheet.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                xlsheet.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;                
                xlbook.SaveAs(filename, Missing.Value, Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                        Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                                        Missing.Value, Missing.Value);               
                System.Windows.Forms.Application.DoEvents();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);                
            }
            finally
            {                
                xlApp.Workbooks.Close();                    
                xlApp.Quit();
                xlApp = null;
                GC.Collect();
            }
        }
        //private System.Data.DataTable Normalize(System.Data.DataTable original)
        //{
        //    System.Data.DataTable dt = new System.Data.DataTable();
        //    dt.Columns.Add("MFG_DATE", typeof(System.String));
        //    dt.Columns.Add("CUST_SNO", typeof(System.String));
        //    dt.Columns.Add("PCB_NO", typeof(System.String));
        //    dt.Columns.Add("x1", typeof(System.Double));
        //    dt.Columns.Add("x2", typeof(System.Double));
        //    dt.Columns.Add("x3", typeof(System.Double));
        //    dt.Columns.Add("x4", typeof(System.Double));
        //    dt.Columns.Add("x5", typeof(System.Double));
        //    dt.Columns.Add("x6", typeof(System.Double));
        //    dt.Columns.Add("x7", typeof(System.Double));
        //    dt.Columns.Add("x8", typeof(System.Double));
        //    dt.Columns.Add("x9", typeof(System.Double));
        //    dt.Columns.Add("x10", typeof(System.Double));
        //    dt.Columns.Add("x11", typeof(System.Double));
        //    dt.Columns.Add("x12", typeof(System.Double));
        //    dt.Columns.Add("x13", typeof(System.Double));
        //    dt.Columns.Add("x14", typeof(System.Double));
        //    dt.Columns.Add("x15", typeof(System.Double));
        //    dt.Columns.Add("x16", typeof(System.Double));
        //    dt.Columns.Add("x17", typeof(System.Double));
        //    dt.Columns.Add("x18", typeof(System.Double));
        //    double[,] mid = new double[original.Columns.Count, 2];
        //    foreach (DataColumn col in original.Columns)
        //    {
 
        //    }
          
        //}
        //private void DataTableToExcel(System.Data.DataTable tempTable, string fileName)
        //{
        //    int rowIndex = 0;
        //    int colIndex = 0;
        //    int rowCount = tempTable.Rows.Count;
        //    int colCount = tempTable.Columns.Count;
        //    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        //    Microsoft.Office.Interop.Excel.Workbook xlbook;
        //    Microsoft.Office.Interop.Excel.Worksheet xlsheet;
        //    try
        //    {
        //        xlbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        //        xlsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlbook.ActiveSheet;
        //        object[,] data = new object[rowCount + 1, colCount];
        //        //System.Data.DataTable dt = (System.Data.DataTable)dataGridView1.DataSource;
        //        //get the title
        //        foreach (DataColumn col in tempTable.Columns)
        //        {
        //            data[rowIndex, colIndex++] = col.ColumnName.ToString();
        //        }
        //        colIndex = 0;
        //        for (rowIndex = 1; rowIndex < rowCount; rowIndex++)
        //        {
        //            for (colIndex = 0; colIndex < colCount; colIndex++)
        //            {

        //                data[rowIndex, colIndex] = tempTable[colIndex, rowIndex - 1].Value;
        //            }
        //        }
        //        Range range = xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[rowCount, colCount]);
        //        range.Value2 = data;
        //        xlsheet.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
        //        xlsheet.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
        //        xlbook.SaveAs(fileName, Missing.Value, Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
        //                                Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
        //                                Missing.Value, Missing.Value);
        //        xlbook.SaveCopyAs(fileName);
        //        System.Windows.Forms.Application.DoEvents();
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.Message);
        //    }
        //    finally
        //    {
        //        xlApp.Workbooks.Close();
        //        xlApp.Quit();
        //        xlApp = null;
        //        GC.Collect();
        //    }
        //}
    }
}